package com.sstock.smartstock;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class StockSelfSelectDB {
	
	private static final String DATABASE_NAME = "stockselfselect.db";
	private static final String DATABASE_TABLE = "stockselfsel";
	private static final int DATABASE_VERSION = 1;
	
	public static final String KEY_ID = "_id";
	public static final String KEY_STOCK_CODE = "stock_code";
	public static final String KEY_STOCK_CODE_SEARCH = "stock_code_search"; 
	public static final String KEY_STOCK_NAME = "stock_name";
	public static final String KEY_CREATION_DATE = "creation_date";
	public static final String KEY_CURRENT_PRICE = "current_price";
	public static final String KEY_FIRST_FOCUS_PRICE= "first_focus_price";
	public static final String KEY_RISE_PRICE = "rise_price";
	public static final String KEY_DROP_PRICE = "drop_price";
	public static final String KEY_SIMULATE_PRICE = "simulate_price";
	public static final String KEY_SIMULATE_NUM = "simulate_num";
	public static final String KEY_NEED_ANALYZE = "need_analyze";
	public static final String KEY_NEED_ALARM   = "need_alarm";
	public static final String KEY_NOTE         = "note";
	
	//when you complete the project, mention that you can dismiss these words like need_alarm to na;
	private static SQLiteDatabase db  = null;
	private Context context;
	private toDoDBOpenHelper dbHelper;
	
	
	public StockSelfSelectDB(RefreshService refreshService) {
		this.context = refreshService;
		dbHelper = new toDoDBOpenHelper(context, DATABASE_NAME,
										null, DATABASE_VERSION);
	}

	public void StockSelfSelectDBAdapter(Context context) {
		this.context = context;
	    dbHelper = new toDoDBOpenHelper(context, DATABASE_NAME, 
	                                    null, DATABASE_VERSION); 
	}
	
	/*
	 * You want to use this Database you must instance your class here
	 */
	/*
	 * RefreshService need to refresh the UI by read from the database if it changed
	 */
	
	/*
	 * Close the database
	 */
	public void close() {
	    db.close();
	}
	
	
	/*
	 * Open the database for write and read implementation  
	 */
	public void open() throws SQLiteException {  
		try {
			db = dbHelper.getWritableDatabase();
	    } catch (SQLiteException ex) {
	        db = dbHelper.getReadableDatabase();
	    }
	}
	
	/*
	 * Insert new stock self select to database
	 */
	public long insertStock(StockFormat _stock) {
		// Create a new row of values to insert.
	    ContentValues newStockValues = new ContentValues();
	    // Assign values for each row.
	    // Add new column in here.
	    newStockValues.put(KEY_STOCK_CODE_SEARCH, _stock.getStock());
	    newStockValues.put(KEY_STOCK_NAME, _stock.getStockName());
	    newStockValues.put(KEY_STOCK_CODE, _stock.getStockCode());
	    newStockValues.put(KEY_CREATION_DATE, _stock.getCreated().getTime());
	    newStockValues.put(KEY_CURRENT_PRICE, _stock.getCurrentPrice());
	    newStockValues.put(KEY_FIRST_FOCUS_PRICE, _stock.getFirstFocusPrice());
	    newStockValues.put(KEY_RISE_PRICE, _stock.getRisePrice());
	    newStockValues.put(KEY_DROP_PRICE, _stock.getDropPrice());
	    newStockValues.put(KEY_SIMULATE_PRICE, _stock.getSimulatePrice());
	    newStockValues.put(KEY_SIMULATE_NUM, _stock.getSimulateNum());
	    newStockValues.put(KEY_NEED_ANALYZE, _stock.getNeedAnalyze());
	    newStockValues.put(KEY_NEED_ALARM, _stock.getNeedAlarm());
	    newStockValues.put(KEY_NOTE, _stock.getNote());
	    
	    // Insert the row.
	    opened();
	    return db.insert(DATABASE_TABLE, null, newStockValues);
	}
	
	/*
	 * Delete stock from the database
	 */
	public boolean removebyIndex(long _rowIndex) {
		//This method doesn't used frequent by that the rowIndex is 
		opened();
	    return db.delete(DATABASE_TABLE, KEY_ID + "=" + _rowIndex, null) > 0;
	}
	
	/*
	 * Delete stock from the database by the stock code
	 */
	public boolean removebyStockcode(String stock_code) {
		//This method maybe used by frequent
		opened();
		return db.delete(DATABASE_TABLE, KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null) > 0;
	}
	
	/*
	 * Delete stock from the database by the stock search code
	 */
	public boolean removebyStockcodesearch(String stock_code_search) {
		//This method very like the removebyStockcode
		opened();
		return db.delete(DATABASE_TABLE, KEY_STOCK_CODE_SEARCH + "=" +"\""+ stock_code_search +"\"", null) > 0;
	}
	
	/*
	 * Update the current_price with stock_code
	 */
	public boolean updateCurrentPriceCode(String stock_code, String _current_price) {
		
	    ContentValues newValue = new ContentValues();
	    newValue.put(KEY_CURRENT_PRICE, _current_price);
	    opened();
	    return db.update(DATABASE_TABLE, newValue,KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null) > 0;
	}
	
	/*
	 * Update the current_price with stock_code_search
	 */
	public boolean updateCurrentPriceCodeSearch(String stock_code_search, String _current_price) {
		
	    ContentValues newValue = new ContentValues();
	    newValue.put(KEY_CURRENT_PRICE, _current_price);
	    opened();
	    return db.update(DATABASE_TABLE, newValue,KEY_STOCK_CODE + "=" +"\""+ stock_code_search +"\"", null) > 0;
	}
	
	/*
	 * Update the RisePrice
	 */
	
	public boolean updateRisePrice(String stock_code, String _rise_price) {
	    ContentValues newValue = new ContentValues();
	    newValue.put(KEY_RISE_PRICE, _rise_price);
	    opened();
	    return db.update(DATABASE_TABLE, newValue,KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null) > 0;
	}
	
	/*
	 * Update the DropPrice
	 */  
	public boolean updateDropPrice(String stock_code, String _drop_price) {
	    ContentValues newValue = new ContentValues();
	    newValue.put(KEY_DROP_PRICE, _drop_price);
	    opened();
	    return db.update(DATABASE_TABLE, newValue, KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null) > 0;
	  }
	
	/**
	 * 
	 * @param stock_code
	 */
	public boolean updateNeedAnalyze(String stock_code,String flag) {
	    ContentValues newValue = new ContentValues();
	    newValue.put(KEY_NEED_ANALYZE, flag);
	    opened();
	    return db.update(DATABASE_TABLE, newValue, KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null) > 0;
	  }
	
	/**
	 * 
	 * @param stock_code
	 * @return
	 * @throws SQLException
	 */
	public boolean updateSimulatNum(String stock_code, String simnum) {
		ContentValues newValue = new ContentValues();
		newValue.put(KEY_SIMULATE_NUM, simnum);
		opened();
		return db.update(DATABASE_TABLE, newValue, KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null) > 0;	
	}
	
	/**
	 * 
	 * @param stock_code
	 * @return
	 * @throws SQLException
	 */
	public boolean updateSimulatePrice(String stock_code, String simprice) {
		ContentValues newValue = new ContentValues();
		newValue.put(KEY_SIMULATE_PRICE, simprice);
		opened();
		return db.update(DATABASE_TABLE, newValue, KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null) > 0;
	}
	
	/**
	 * 
	 * @param stock_code
	 * @return
	 * @throws SQLException
	 */
	public boolean updateNotes(String stock_code, String remark) {
		ContentValues newValue = new ContentValues();
		newValue.put(stock_code, remark);
		opened();
		return db.update(DATABASE_TABLE, newValue, KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null) > 0;
	}
	
	/*
	 * Set the cursor to key stock by quote the stock_code
	 */
	public Cursor setCursorToKeyStock(String stock_code) throws SQLException {
		Cursor result = db.query(true, DATABASE_TABLE,
				                 new String[] {KEY_ID, KEY_STOCK_CODE,KEY_STOCK_NAME,KEY_CURRENT_PRICE},
				                 KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null, null, null,
				                 null,null);
		if((result.getCount() == 0) || !result.moveToFirst()) {
			//throw new SQLException("No Key_STOCK items find for now: " + stock_code);
			return null;
		}
		return result;
	}
	
	/*
	 * Set the cursor to key stock by quote the stock_code_search
	 */
	public Cursor setCursorToSearch(String stock_code_search) throws SQLException {
		Cursor result = db.query(true, DATABASE_TABLE,
				                 new String[] {KEY_ID, KEY_STOCK_CODE,KEY_STOCK_NAME,KEY_CURRENT_PRICE},
				                 KEY_STOCK_CODE_SEARCH + "=" +"\""+ stock_code_search +"\"", null, null, null,
				                 null,null);
		if((result.getCount() == 0) || !result.moveToFirst()) {
			//throw new SQLException("No Key_STOCK items find for now: " + stock_code_search);
			return null;
			//so return null may have problems because that null may be produced by myself
			//!! this will help to debug those I Log uncaught the finalize XXXXXX
		}
		return result;
	}
	
	/*
	 * Get all the database value
	 */
	public Cursor getAllStockFormatCursor() {
		opened();
	    return db.query(DATABASE_TABLE, 
	                    new String[] { KEY_ID, KEY_STOCK_CODE_SEARCH, KEY_STOCK_CODE, KEY_STOCK_NAME, KEY_CREATION_DATE, KEY_CURRENT_PRICE, KEY_FIRST_FOCUS_PRICE, KEY_RISE_PRICE, KEY_DROP_PRICE,
	    							   KEY_SIMULATE_PRICE, KEY_SIMULATE_NUM}, 
	                    null, null, null, null, null);
	}
	
	/*
	 * Get the cursor of what you want to search by stock code 
	 */
	public Cursor getStockCursor(String stock_code) throws SQLException {
		opened();
	    Cursor cursor = db.query(true, DATABASE_TABLE, 
	                             new String[] {KEY_ID, KEY_STOCK_CODE_SEARCH, KEY_STOCK_CODE, KEY_STOCK_NAME, KEY_CREATION_DATE, KEY_CURRENT_PRICE,KEY_FIRST_FOCUS_PRICE,KEY_RISE_PRICE,KEY_DROP_PRICE},
	                             KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null, null, null, 
	                             null, null);
	    if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
	      //throw new SQLException("No stock item found for row: " + stock_code);
	    	return null;
	    }
			  
	    return cursor;  
	  }
	
	/**
	 * Get the cursor of what you want to search by stock code search
	 */
	public Cursor getStockSearchCursor(String stock_code_search) throws SQLException {
		opened();
	    Cursor cursor = db.query(true, DATABASE_TABLE, 
	                             new String[] {KEY_ID, KEY_STOCK_CODE_SEARCH, KEY_STOCK_CODE, KEY_STOCK_NAME, KEY_CREATION_DATE, KEY_CURRENT_PRICE,KEY_FIRST_FOCUS_PRICE,KEY_RISE_PRICE,KEY_DROP_PRICE},
	                             KEY_STOCK_CODE_SEARCH + "=" +"\""+ stock_code_search +"\"", null, null, null, 
	                             null, null);
	    if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
	      throw new SQLException("No stock item found for row: " + stock_code_search);
	    }
			  
	    return cursor;  
	  }
	
	/**
	 * Get the cursor of what you want to analyze
	 */
	public Cursor getNeedAnalyzeCursor() throws SQLException {
		opened();
		Cursor cursor = db.query(true, DATABASE_TABLE,
								 new String[] {KEY_ID, KEY_STOCK_CODE, KEY_CURRENT_PRICE, KEY_RISE_PRICE, KEY_DROP_PRICE, KEY_NEED_ANALYZE},
								 KEY_NEED_ANALYZE + "=" +"\"" + "T" + "\"", null, null, null,
								 null, null);
		if((cursor.getCount() == 0) || !cursor.moveToFirst()) {
			return cursor;
		}
		
		return cursor;
	}
	
	/**
	 * Get the String of the flag that you want to analyze
	 */
	public String getNeedAnalyzeFlag(String stock_code) throws SQLException {
		opened();
		String tempflag;
		
		Cursor cursor = db.query(true, DATABASE_TABLE,
				new String[] {KEY_ID, KEY_STOCK_CODE, KEY_NEED_ANALYZE},
				KEY_STOCK_CODE + "=" + "\"" + stock_code + "\"", null, null, null,
				null, null);
		if((cursor.getCount() == 0) || !cursor.moveToFirst()) {
			tempflag = "";
		}
		else
		{
			tempflag = cursor.getString(cursor.getColumnIndex(StockSelfSelectDB.KEY_NEED_ANALYZE));
			cursor.close();
		}
		return tempflag;
	}
	/**
	 * return current price of the stock
	 */
	public String getStockCurrentPrice(String stock_code) throws SQLException {
		opened();
		String current_price ;
		Cursor cursor = db.query(true, DATABASE_TABLE, 
                new String[] {KEY_ID, KEY_STOCK_CODE, KEY_CURRENT_PRICE},
                KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null, null, null, 
                null, null);
		if((cursor.getCount() == 0) || !cursor.moveToFirst()) {
			current_price = "";
		}
		else
		{
			current_price = cursor.getString(cursor.getColumnIndex(StockSelfSelectDB.KEY_CURRENT_PRICE));
			cursor.close();
		}
		return current_price;
	}
	
	/**
	 * return current Notes of the stock
	 */
	public String getStockNotes(String stock_code) throws SQLException {
		opened();
		String current_notes;
		Cursor cursor = db.query(true, DATABASE_TABLE,
				new String[] {KEY_ID, KEY_STOCK_CODE, KEY_NOTE},
				KEY_STOCK_CODE + "=" +"\""+ stock_code +"\"", null, null, null,
				null, null);
		if((cursor.getCount() == 0) || !cursor.moveToFirst()) {
			current_notes = "";
		}
		else
		{
			current_notes = cursor.getString(cursor.getColumnIndex(StockSelfSelectDB.KEY_CURRENT_PRICE));
			cursor.close();
		}
		return current_notes;
	}
	/**
	 * Get the cursor of what need alarm
	 */
	public Cursor getNeedAlarm() throws SQLException {
		opened();
		Cursor cursor = db.query(true, DATABASE_TABLE,
								 new String[] {},
								 "", null, null, null,
								 null, null);
		if((cursor.getCount() == 0) || !cursor.moveToFirst()) {
			return null;
		}
		return cursor;
	}
	/**
	 * Test the database isOpened or not
	 */
	public void opened()
	{
		if(db == null || !db.isOpen())
		{
			db = dbHelper.getWritableDatabase();
		}
	}
	
	/*
	 * Create the database use SQLite
	 */
	private static class toDoDBOpenHelper extends SQLiteOpenHelper {

		  public toDoDBOpenHelper(Context context, String name,
		                          CursorFactory factory, int version) {
			  	super(context, name, factory, version);
		  }

		  // SQL Statement to create a new database.
		  private static final String DATABASE_CREATE = "create table " + 
		    	DATABASE_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " +
		    	KEY_STOCK_CODE_SEARCH + " text not null, " + KEY_STOCK_CODE + " text not null, " + 
		    	KEY_STOCK_NAME + " text not null, " + KEY_CREATION_DATE + " long, " + KEY_CURRENT_PRICE + " text not null, " + 
		    	KEY_FIRST_FOCUS_PRICE + " text not null, " + KEY_RISE_PRICE + " text not null, " + KEY_DROP_PRICE + " text not null, " + 
		    	KEY_SIMULATE_PRICE + " text not null, " + KEY_SIMULATE_NUM + " text not null, " + KEY_NEED_ANALYZE + " text not null, " +
		    	KEY_NEED_ALARM + " text not null, " + KEY_NOTE + " text not null);";

		  @Override
		  public void onCreate(SQLiteDatabase _db) {
			  _db.execSQL(DATABASE_CREATE);
		  }

		  @Override
		  public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
			  Log.v("TaskDBAdapter", "Upgrading from version " + 
		                           _oldVersion + " to " +
		                           _newVersion + ", which will destroy all old data");

			  // Drop the old table.
			  _db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
			  // Create a new one.
			  onCreate(_db);
		  }
	}
	
}
